Overview of CRUD operations using ASP.NET GridView
Crud operation ( create, read, update, and delete) is another term of the Select
, Insert
, Update
, and Delete
actions. In this example, we will use a simple database table to perform these operations from an ASP.NET GridView.
Let’s get started by creating the database and the table.
The Database
Now create a simple table called customers with the following structure.
CustomerId (int, not null)
CustomerName (nvarchar(50), null)
CustomerEmail (nvarchar(50), null)
Or simply run the query below:
SET ansi_nulls ON go SET quoted_identifier ON go CREATE TABLE [dbo].[customers] ( [customerid] [INT] IDENTITY(1, 1) NOT NULL, [customername] [NVARCHAR](50) NULL, [customeremail] [NVARCHAR](50) NULL ) ON [PRIMARY] go
HTML
For the view part, create an Asp.Net GridView with necessary buttons for insert, update, cancel, and delete operations.
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" CssClass="gv" HeaderStyle-CssClass="gvHeader" FooterStyle-CssClass="gvHeader"> <Columns> <asp:TemplateField HeaderText="ID" ItemStyle-Width="150px"> <ItemTemplate> <asp:Label Text='<%# Eval("CustomerId") %>' runat="server" /> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Name" ItemStyle-Width="150px" ItemStyle-CssClass="Name"> <ItemTemplate> <asp:Label Text='<%# Eval("CustomerName") %>' runat="server" /> <asp:TextBox Text='<%# Eval("CustomerName") %>' runat="server" Style="display: none" /> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Email" ItemStyle-Width="150px" ItemStyle-CssClass="Email"> <ItemTemplate> <asp:Label Text='<%# Eval("CustomerEmail") %>' runat="server" /> <asp:TextBox Text='<%# Eval("CustomerEmail") %>' runat="server" Style="display: none" /> </ItemTemplate> </asp:TemplateField> <asp:TemplateField> <ItemTemplate> <asp:LinkButton Text="Edit" ID="Edit" runat="server" CssClass="Edit" /> <asp:LinkButton Text="Update" ID="Update" runat="server" CssClass="Update" Style="display: none" /> <asp:LinkButton Text="Cancel" ID="Cancel" runat="server" CssClass="Cancel" Style="display: none" /> <asp:LinkButton Text="Delete" ID="Delete" runat="server" CssClass="Delete" /> </ItemTemplate> </asp:TemplateField> </Columns> <RowStyle CssClass="gvItem" /> </asp:GridView> <br /> <label>Add new customer</label> <table border="1" style="margin: 2px"> <tr> <td style="width: 150px;"><b>Name</b><br /> <asp:TextBox ID="txtName" runat="server" Width="140" /> </td> <td style="width: 150px;"><b>Email</b><br /> <asp:TextBox ID="txtEmail" runat="server" Width="140" /> </td> <td style="width: 100px"> <br /> <asp:Button ID="btnAdd" runat="server" Text="Add" /> </td> </tr> </table> <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
The CSS
<style> .gv { border: solid 2px black; } .header { background-color: #646464; font-family: Arial; color: White; border: none 0px transparent; height: 25px; text-align: center; font-size: 16px; } .rows { background-color: #fff; font-family: Arial; font-size: 14px; color: #000; min-height: 25px; text-align: left; border: none 0px transparent; } .selectedrow { background-color: #ff8000; font-family: Arial; color: #fff; font-weight: bold; text-align: left; } .pager { background-color: #646464; font-family: Arial; color: White; height: 30px; text-align: left; } .gv td { padding: 5px; } .gv th { padding: 5px; } </style>
Methods to handle Data
Load the Data
Below is the method we are going to use to load the data into the GridView.
private void BindCustomers() { string conn_str = "Data Source=ServerName;Initial Catalog=customersdb;Integrated Security=True"; using (SqlConnection con = new SqlConnection(conn_str)) { using (SqlCommand cmd = new SqlCommand()) { cmd.CommandText = "SELECT * FROM Customers"; cmd.Connection = con; using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); sda.Fill(dt); gvCustomers.DataSource = dt; gvCustomers.DataBind(); } } } }
Now call this method from the page_load method
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { BindCustomers(); } }
And add a method to handle the Paging
protected void OnPaging(object sender, GridViewPageEventArgs e) { gvCustomers.PageIndex = e.NewPageIndex; BindCustomers(); }
The page should look like this after you run the application:
InsertCustomer
Add the function below to the code behind to handle the inserting of a new customer to the Database.
[WebMethod] public static int InsertCustomer(string customerName, string customerEmail) { string conn_str = "Data Source=ServerName;Initial Catalog=customersdb;Integrated Security=True"; using (SqlConnection con = new SqlConnection(conn_str)) { using (SqlCommand cmd = new SqlCommand("INSERT INTO Customers VALUES(@customerName, @customerEmail)")) { cmd.Parameters.AddWithValue("@customerName", customerName); cmd.Parameters.AddWithValue("@customerEmail", customerEmail); cmd.Connection = con; con.Open(); int customerId = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); return customerId; } } }
UpdateCustomer
Add the function below to the code behind to handle updating an existing customer.
[WebMethod] public static void UpdateCustomer(int customerId, string customerName, string customerEmail) { string connStr = "Data Source=ServerName;Initial Catalog=customersdb;Integrated Security=True"; using (SqlConnection con = new SqlConnection(connStr)) { using (SqlCommand cmd = new SqlCommand("UPDATE Customers SET CustomerName = @customerName, CustomerEmail = @customerEmail WHERE CustomerId = @CustomerId")) { cmd.Parameters.AddWithValue("@CustomerId", customerId); cmd.Parameters.AddWithValue("@customerName", customerName); cmd.Parameters.AddWithValue("@customerEmail", customerEmail); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); con.Close(); } } }
DeleteCustomer
Now add one more function to the code behind to handle deleting a customer.
[WebMethod] public static void DeleteCustomer(int customerId) { string connStr = "Data Source=ServerName;Initial Catalog=customersdb;Integrated Security=True"; using (SqlConnection con = new SqlConnection(connStr)) { using (SqlCommand cmd = new SqlCommand("DELETE FROM Customers WHERE CustomerId = @CustomerId")) { cmd.Parameters.AddWithValue("@CustomerId", customerId); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); con.Close(); } } }
Add the Ajax Calls
Now add the Ajax Calls from the ASPX file to handle the client-side events:
<script> $("[id*=gvCustomers]").find("[id*=Cancel]").click(function () { var row = $(this).closest("tr"); $("td", row).each(function () { if ($(this).find("input").length > 0) { var span = $(this).find("span"); var input = $(this).find("input"); input.val(span.html()); span.show(); input.hide(); } }); row.find(".Edit").show(); row.find(".Delete").show(); row.find(".Update").hide(); $(this).hide(); return false; }); $("[id*=gvCustomers]").find("[id*=Delete]").click(function () { if (confirm("Do you want to delete this customer?")) { var row = $(this).closest("tr"); var customerId = row.find("span").html(); $.ajax({ type: "POST", url: "Default.aspx/DeleteCustomer", data: '{customerId: ' + customerId + '}', contentType: "application/json; charset=utf-8", dataType: "json", success: function (response) { alert('deleted') row.remove(); } }); } }); $("[id*=gvCustomers]").find("[id*=Edit]").click(function () { var row = $(this).closest("tr"); $("td", row).each(function () { if ($(this).find("input").length > 0) { $(this).find("input").show(); $(this).find("span").hide(); } }); row.find(".Update").show(); row.find(".Cancel").show(); row.find(".Delete").hide(); $(this).hide(); return false; }); $("[id*=gvCustomers]").find("[id*=Update]").click(function () { var row = $(this).closest("tr"); $("td", row).each(function () { if ($(this).find("input").length > 0) { var span = $(this).find("span"); var input = $(this).find("input"); span.html(input.val()); span.show(); input.hide(); } }); row.find(".Edit").show(); row.find(".Delete").show(); row.find(".Cancel").hide(); $(this).hide(); var customerId = row.find("td").eq(0).find("span").html(); var customerName = row.find("td").eq(1).find("span").html(); var customerEmail = row.find("td").eq(2).find("span").html(); $.ajax({ type: "POST", url: "Default.aspx/UpdateCustomer", data: '{customerId: ' + customerId + ', customerName: "' + customerName + '", customerEmail: "' + customerEmail + '" }', contentType: "application/json; charset=utf-8", dataType: "json" }); return false; }); $("[id*=btnAdd]").click(function () { var txtName = $("[id*=txtName]"); var txtEmail = $("[id*=txtEmail]"); if (txtName.val() == '' || txtEmail.val() == '') { alert('Name and Email are required') return; } $.ajax({ type: "POST", url: "Default.aspx/InsertCustomer", data: '{customerName: "' + txtName.val() + '", customerEmail: "' + txtEmail.val() + '" }', contentType: "application/json; charset=utf-8", dataType: "json", success: function (response) { txtName.val(""); txtEmail.val(""); } }); return false; }); </script>
Run the application.
Happy coding!!!
Comments